package IDbConnections;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.BusinessRule;
import Model.BusinessRuleManager;
import Model.Constraint;

public class SourceDatabase {
	
	static String userid="THO7_2010_4B_TEAM3", password = "THO7_2010_4B_TEAM3";
	static String url = "jdbc:oracle:thin:@ondora01.hu.nl:8521:cursus01";
	static Statement stmt;
	static PreparedStatement pstmt;
	static Connection con;

	
	
    public SourceDatabase() {}
    
	public static Connection getConnection()
	{		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			} 
		catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
		} 
		catch(SQLException ex) {
			System.err.println("SQLException:  " + ex.getMessage());
		}
		
		return con;
	}

	public BusinessRule getTrigger(Connection con, BusinessRule b) throws SQLException{
		try{
			Statement tstmt = con.createStatement();
			ResultSet trs = tstmt.executeQuery("SELECT t.TWHEN, t.ACTION, ta.NAME FROM BR_TRIGGER t, BR_TABLE ta, BUSINESS_RULE br WHERE t.BUSINESS_RULE_ID = " + b.getBusinessRuleID() + " AND FOR_TABLE = BR_TABLE_ID");
			if(trs.next()){
				String[] action = trs.getString(2).split(" ");
				b.setTrigger(trs.getString(1), action, trs.getString(3));
			} else {
				System.out.println("helaas geen resultaten voor trigger");
			}
			return b;
		} catch(SQLException sqe){
			sqe.printStackTrace();
			return b;
		}
		
	}
	
	public ArrayList<Constraint> getConstraints(Connection con, int operatorid){
		ArrayList<Constraint> constraints = new ArrayList<Constraint>();
		try{
			Statement tstmt = con.createStatement();
			ResultSet trs = tstmt.executeQuery("SELECT co.sequence, co.QUERY, co.STATICNUMBER, co.STATICVALUE, ta.name, tc.column_name FROM BR_CONSTRAINT co,  BR_TABLE ta, TABLE_COLUMN tc	WHERE co.for_table = ta.br_table_id (+) AND co.for_column = tc.table_column_id (+) AND BR_OPERATOR_ID = "+ operatorid +" ORDER BY co.sequence");			
			
			while(trs.next()){
				constraints.add(new Constraint(trs.getInt(1), trs.getString(2), trs.getString(3), trs.getString(4), trs.getString(5), trs.getString(6)));
			}
			
		} catch(SQLException sqe){
			sqe.printStackTrace();
		}
		return constraints;		
	}
    
    public void getBusinessRules (BusinessRuleManager BRList) {
    	
    	Connection con = getConnection();
		
//        String sql1 = "SELECT BUSINESS_RULE_TYPE.TYPE, BUSINESS_RULE.BUSINESS_RULE_ID, " +
//        			 "BUSINESS_RULE.NAME AS BUSINESS_RULE_NAME, BR_ERROR.MESSAGE AS ERROR_MESSAGE," +
//        			 " BR_TABLE.NAME AS TABLE_NAME, TABLE_COLUMN.COLUMN_NAME, BR_OPERATOR.OPERATOR " +
//        			 "FROM BUSINESS_RULE_TYPE, BUSINESS_RULE, BR_ERROR, BR_TABLE, TABLE_COLUMN, BR_OPERATOR, BR_TRIGGER " +
//        			 "WHERE BUSINESS_RULE.BUSINESS_RULE_TYPE_ID = BUSINESS_RULE_TYPE.BUSINESS_RULE_TYPE_ID " +
//        			 "AND BUSINESS_RULE.HAS_ERROR = BR_ERROR_ID " +
//        			 "AND BUSINESS_RULE.HAS_OPERATOR = BR_OPERATOR.BR_OPERATOR_ID " +
//        			 "AND BR_OPERATOR.HAS_TABLE = BR_TABLE.BR_TABLE_ID " +
//        			 "AND TABLE_COLUMN.BR_TABLE_ID = BR_TABLE.BR_TABLE_ID";
        
        String sql = "SELECT brt.type, br.business_rule_id, br.name, err.message, " +
                      	"ta.name, tc.column_name, op.operator, op.BR_operator_id "+

                     "FROM BUSINESS_RULE_TYPE brt, BUSINESS_RULE br, BR_ERROR err, BR_OPERATOR op, BR_TABLE ta, " +
                     	"TABLE_COLUMN tc " +

                     "WHERE brt.business_rule_type_id = br.business_rule_type_id " + 
                     	"AND br.has_error = err.br_error_id " +
                     	"AND br.has_operator = op.br_operator_id "+
                     	"AND op.has_table = ta.br_table_id "+
                     	"AND op.has_column = tc.table_column_id " +
                     "ORDER BY br.business_rule_id";
        
 
        System.out.println("Executing SQL: " + sql);
        
        try {
			stmt = con.createStatement();
	        ResultSet rs = stmt.executeQuery(sql);
	        while (rs.next()) {
	            System.out.println("result [" + rs.getRow() + "]: " + rs.getString(1) + ", " + rs.getInt(2) + ", " + rs.getString(3) + ", " +
	            								 rs.getString(4) + ", " + rs.getString(5) + ", " + rs.getString(6) + ", " + rs.getString(7));
	            //String[] constrain = getConstraints(con, rs.getInt(8));
	            
	            ArrayList<Constraint> constrain = getConstraints(con, rs.getInt(8));;
	            BusinessRule insertBR = new BusinessRule(rs.getInt(2), rs.getString(1), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), constrain);
	            
	            insertBR = getTrigger(con, insertBR);
	            
	            BRList.addBusinessRuleForSelecting(insertBR);
	        }
	        stmt.close();
		    con.close();
		} 
		catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
        System.out.println("BusinessRule(s) added to object(s)...");
		System.out.println("finished...");
        System.out.println("-------------------------------------------------");
    }
    
}
